﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using CoreLab.Oracle; //Devart.Data.Oracle; //using Oracle.DataAccess.Client;
using System.Web;
using System.Web.Caching;


using LMD.Constant;
using LMD.Lib.Oracle;

namespace Cached
{
    /// <summary>
    /// Summary description for CompanyReport_AktivitasCARSalesTable
    /// </summary>
    public class CompanyReport_AktivitasCARSalesTable : CacheAbstraction
    {
        public DataTable GetAktivitasCARSalesRekap(int iCompanyId, DateTime dtmStart, DateTime dtmEnd)
        {
            return GetAktivitasCARSalesRekap(iCompanyId, dtmStart, dtmEnd, false);
        }
        public DataTable GetAktivitasCARSalesRekap(int iCompanyId, DateTime dtmStart, DateTime dtmEnd, bool bForceRefresh)
        {
            String sxCacheKey = String.Format("{0}{1}_{2}_{3}",
                KCache.V_REPORT_AKTIVITAS_CAR_SALES_REKAP, 
                iCompanyId.ToString(),
                dtmStart.ToShortDateString(),
                dtmEnd.ToShortDateString());

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib();

                List<OracleParameter> lsParams = new List<OracleParameter>();

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmID";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iCompanyId;

                //lsParams.Add(new OracleParameter());
                //lsParams[lsParams.Count - 1].ParameterName = "prmStart";
                //lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                //lsParams[lsParams.Count - 1].Value = dtmStart;

                //lsParams.Add(new OracleParameter());
                //lsParams[lsParams.Count - 1].ParameterName = "prmEnd";
                //lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                //lsParams[lsParams.Count - 1].Value = dtmEnd;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmStartMonth";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = dtmStart.Month;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmStartYear";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = dtmStart.Year;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmEndMonth";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = dtmEnd.Month;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmEndYear";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = dtmEnd.Year;

                String sxSelect =
                    "count(T_COURTESY_CALL_ID) as \"TOTAL COURTESY CALL CAR\", " +
                    "count(T_CUSTOMER_VISIT_ID) \"TOTAL CUSTOMER VISIT CAR\", "+
                    "count(KONFIG_FILE_ID) \"TOTAL FILE KONFIGURASI\", "+
                    "count(DATEK_FILE_ID) \"TOTAL FILE DATEK\", "+
                    "count(EVALUASI_FILE_ID) \"TOTAL FILE EVALUASI\", "+
                    "count(CUSTOMER_VISIT) \"TOTAL CUSTOMER VISIT SALES\", "+
                    "count(PROPOSAL_PRESENTASI) \"TOTAL PROPOSAL PRESENTASI\", "+
                    "MONTH_NUM, "+
                    "YEAR_NUM, "+
                    "CUSTOMER_ID";
                String sxQuery = String.Format(
                    @"
                        select
                            {0}
                        from
                            (
                                select
                                    t1.T_COURTESY_CALL_ID,
                                    null T_CUSTOMER_VISIT_ID,
                                    null KONFIG_FILE_ID,
                                    null DATEK_FILE_ID,
                                    null EVALUASI_FILE_ID,
                                    null CUSTOMER_VISIT,
                                    null PROPOSAL_PRESENTASI,
                                    t1.CONTACT_PERSON_ID,
                                    t1.NEW_CONTACT_PERSON_ID,
                                    cast(to_char(t1.REALIZATION_DATE, 'MM') as number) MONTH_NUM,
                                    cast(to_char(t1.REALIZATION_DATE, 'YYYY') as number) YEAR_NUM,
                                    v1.CUSTOMER_ID
                                from
                                    t_courtesy_call t1
                                join
                                (
                                    select 
                                        pic.SITE_PIC_ID CONTACT_PERSON_ID,
                                        --pic.PIC_NAME NAME, 
                                        'N' IS_NEW_CP,
                                        comp.COMPANY_ID CUSTOMER_ID,
                                        comp.COMPANY_NAME
                                    from 
                                        SNI.SITE_PIC pic
                                    LEFT JOIN
                                       sni.site
                                    ON 
                                        pic.site_id = site.site_id
                                    left join
                                        sni.company comp
                                    on
                                        site.company_id = comp.company_id
                                    where
                                        NVL (pic.VALID_TO, SYSDATE + 1) > SYSDATE
                                    UNION ALL
                                    SELECT  
                                        ncp.NEW_CONTACT_PERSON_ID,
                                        --ncp.NAME,
                                        'Y' IS_NEW_CP,
                                        ncp.CUSTOMER_ID,
                                        comp.COMPANY_NAME
                                    FROM      
                                        NEW_CONTACT_PERSON ncp
                                    LEFT JOIN
                                       sni.company comp
                                    ON 
                                       ncp.CUSTOMER_ID = comp.company_id 
								    UNION ALL
                                    SELECT (cpc.COMPANY_PIC_ID + 1000000000) CONTACT_PERSON_ID,
                                        'N' IS_NEW_CP,
                                         comp.COMPANY_ID CUSTOMER_ID,
                                         comp.COMPANY_NAME
                                    FROM sni.COMPANY_PIC cpc
                                        JOIN sni.company comp
                                        ON CPC.COMPANY_ID = comp.COMPANY_ID
                                    WHERE NVL(cpc.VALID_TO,SYSDATE) >= SYSDATE
                                )v1
                                    on (t1.CONTACT_PERSON_ID = v1.CONTACT_PERSON_ID and v1.IS_NEW_CP = 'N')
                                    or (t1.NEW_CONTACT_PERSON_ID = v1.CONTACT_PERSON_ID and v1.IS_NEW_CP = 'Y')
                                where
                                    t1.call_status = 'REALIZED'
                                    and 
                                    (
                                        to_date('1\' || :prmStartMonth || '\' || :prmStartYear, 'dd\mm\yyyy') <= t1.REALIZATION_DATE
                                        and
                                        ADD_MONTHS(to_date('1\' || :prmEndMonth || '\' || :prmEndYear, 'dd\mm\yyyy'), 1) > t1.REALIZATION_DATE
                                    )
                                union all
                                select
                                    null T_COURTESY_CALL_ID,
                                    t1.T_CUSTOMER_VISIT_ID,
                                    null KONFIG_FILE_ID,
                                    null DATEK_FILE_ID,
                                    null EVALUASI_FILE_ID,
                                    null CUSTOMER_VISIT,
                                    null PROPOSAL_PRESENTASI,
                                    t1.CONTACT_PERSON_ID,
                                    t1.NEW_CONTACT_PERSON_ID,
                                    cast(to_char(t1.VISIT_DATETIME, 'MM') as number) MONTH_NUM,
                                    cast(to_char(t1.VISIT_DATETIME, 'YYYY') as number) YEAR_NUM,
                                    v1.CUSTOMER_ID
                                from
                                    t_customer_visit t1
                                join
                                (
                                    select 
                                        pic.SITE_PIC_ID CONTACT_PERSON_ID,
                                        --pic.PIC_NAME NAME, 
                                        'N' IS_NEW_CP,
                                        comp.COMPANY_ID CUSTOMER_ID,
                                        comp.COMPANY_NAME
                                    from 
                                        SNI.SITE_PIC pic
                                    LEFT JOIN
                                       sni.site
                                    ON 
                                        pic.site_id = site.site_id
                                    left join
                                        sni.company comp
                                    on
                                        site.company_id = comp.company_id
                                    where
                                        NVL (pic.VALID_TO, SYSDATE + 1) > SYSDATE
                                    UNION ALL
                                    SELECT  
                                        ncp.NEW_CONTACT_PERSON_ID,
                                        --ncp.NAME,
                                        'Y' IS_NEW_CP,
                                        ncp.CUSTOMER_ID,
                                        comp.COMPANY_NAME
                                    FROM      
                                        NEW_CONTACT_PERSON ncp
                                    LEFT JOIN
                                       sni.company comp
                                    ON 
                                       ncp.CUSTOMER_ID = comp.company_id 
								    UNION ALL
                                    SELECT (cpc.COMPANY_PIC_ID + 1000000000) CONTACT_PERSON_ID,
                                        'N' IS_NEW_CP,
                                         comp.COMPANY_ID CUSTOMER_ID,
                                         comp.COMPANY_NAME
                                    FROM sni.COMPANY_PIC cpc
                                        JOIN sni.company comp
                                        ON CPC.COMPANY_ID = comp.COMPANY_ID
                                    WHERE NVL(cpc.VALID_TO,SYSDATE) >= SYSDATE
                                )v1
                                    on (t1.CONTACT_PERSON_ID = v1.CONTACT_PERSON_ID and v1.IS_NEW_CP = 'N')
                                    or (t1.NEW_CONTACT_PERSON_ID = v1.CONTACT_PERSON_ID and v1.IS_NEW_CP = 'Y')
                                where
                                    t1.visit_status = 'REALIZED'
                                    and 
                                    (
                                        to_date('1\' || :prmStartMonth || '\' || :prmStartYear, 'dd\mm\yyyy') <= t1.VISIT_DATETIME
                                        and
                                        ADD_MONTHS(to_date('1\' || :prmEndMonth || '\' || :prmEndYear, 'dd\mm\yyyy'), 1) > t1.VISIT_DATETIME
                                    )
                                union all
                                select
                                    null T_COURTESY_CALL_ID,
                                    null T_CUSTOMER_VISIT_ID,
                                    KONFIG_FILE_ID,
                                    DATEK_FILE_ID,
                                    EVALUASI_FILE_ID,
                                    null CUSTOMER_VISIT,
                                    null PROPOSAL_PRESENTASI,
                                    null CONTACT_PERSON_ID,
                                    null NEW_CONTACT_PERSON_ID,
                                    cast(to_char(CREATE_DATE, 'MM') as number) MONTH_NUM,
                                    cast(to_char(CREATE_DATE, 'YYYY') as number) YEAR_NUM,
                                    CUSTOMER_ID
                                from
                                    T_KONFIGURASI_DATEK
                                where
                                    to_date('1\' || :prmStartMonth || '\' || :prmStartYear, 'dd\mm\yyyy') <= CREATE_DATE
                                    and
                                    ADD_MONTHS(to_date('1\' || :prmEndMonth || '\' || :prmEndYear, 'dd\mm\yyyy'), 1) > CREATE_DATE
                                union all
                                select
                                    null T_COURTESY_CALL_ID,
                                    null T_CUSTOMER_VISIT_ID,
                                    null KONFIG_FILE_ID,
                                    null DATEK_FILE_ID,
                                    null EVALUASI_FILE_ID,
                                    slx.t_slx_activity_id CUSTOMER_VISIT,
                                    null PROPOSAL_PRESENTASI,
                                    null CONTACT_PERSON_ID,
                                    null NEW_CONTACT_PERSON_ID,
                                    cast(to_char(slx.UPDATE_DATE, 'MM') as number) MONTH_NUM,
                                    cast(to_char(slx.UPDATE_DATE, 'YYYY') as number) YEAR_NUM,
                                    comp.COMPANY_ID CUSTOMER_ID
                                from
                                    slogix.t_slx_activity slx
                                join
                                    sni.COMPANY comp
                                    on
                                    slx.ACCOUNT_NAME = COMP.COMPANY_NAME
                                where
                                    p_activity_type_id = 11
                                    and
                                    TO_DATE ('1\' || :prmStartMonth || '\' || :prmStartYear, 'dd\mm\yyyy') <= slx.UPDATE_DATE
                                    AND ADD_MONTHS (TO_DATE ('1\' || :prmEndMonth || '\' || :prmEndYear,'dd\mm\yyyy'), 1) > slx.UPDATE_DATE
                                union all
                                select
                                    null T_COURTESY_CALL_ID,
                                    null T_CUSTOMER_VISIT_ID,
                                    null KONFIG_FILE_ID,
                                    null DATEK_FILE_ID,
                                    null EVALUASI_FILE_ID,
                                    null CUSTOMER_VISIT,
                                    slx.t_slx_activity_id PROPOSAL_PRESENTASI,
                                    null CONTACT_PERSON_ID,
                                    null NEW_CONTACT_PERSON_ID,
                                    cast(to_char(slx.UPDATE_DATE, 'MM') as number) MONTH_NUM,
                                    cast(to_char(slx.UPDATE_DATE, 'YYYY') as number) YEAR_NUM,
                                    comp.COMPANY_ID CUSTOMER_ID
                                from
                                    slogix.t_slx_activity slx
                                join
                                    sni.COMPANY comp
                                    on
                                    slx.ACCOUNT_NAME = COMP.COMPANY_NAME
                                where
                                    p_activity_type_id = 10
                                    and
                                    TO_DATE ('1\' || :prmStartMonth || '\' || :prmStartYear, 'dd\mm\yyyy') <= slx.UPDATE_DATE
                                    AND ADD_MONTHS (TO_DATE ('1\' || :prmEndMonth || '\' || :prmEndYear,'dd\mm\yyyy'), 1) > slx.UPDATE_DATE    
                            )   
                        where CUSTOMER_ID = :prmID
                        group by
                            MONTH_NUM,
                            YEAR_NUM,
                            CUSTOMER_ID
                        order by
                            YEAR_NUM desc, MONTH_NUM desc
                    ",sxSelect);

                DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());

                AddToCache(sxCacheKey, dt, CacheType.Daily);
                
            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }

        public DataTable GetAktivitasCARSalesDetail(int iCompanyId, DateTime dtmStart, DateTime dtmEnd)
        {
            return GetAktivitasCARSalesDetail(iCompanyId, dtmStart, dtmEnd, false);
        }
        public DataTable GetAktivitasCARSalesDetail(int iCompanyId, DateTime dtmStart, DateTime dtmEnd, bool bForceRefresh)
        {
            String sxCacheKey = String.Format("{0}{1}_{2}_{3}",
                KCache.V_REPORT_AKTIVITAS_CAR_SALES_DETAIL,
                iCompanyId.ToString(),
                dtmStart.ToShortDateString(),
                dtmEnd.ToShortDateString());

            if(bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib();

                List<OracleParameter> lsParams = new List<OracleParameter>();

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmID";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iCompanyId;

                //lsParams.Add(new OracleParameter());
                //lsParams[lsParams.Count - 1].ParameterName = "prmStart";
                //lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                //lsParams[lsParams.Count - 1].Value = dtmStart;

                //lsParams.Add(new OracleParameter());
                //lsParams[lsParams.Count - 1].ParameterName = "prmEnd";
                //lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                //lsParams[lsParams.Count - 1].Value = dtmEnd;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmStartMonth";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = dtmStart.Month;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmStartYear";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = dtmStart.Year;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmEndMonth";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = dtmEnd.Month;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmEndYear";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = dtmEnd.Year;

                String sxQuery =
                    @"
                    select
                            CUSTOMER_ID,
                            MONTH_NUM,
                            YEAR_NUM,
                            count(T_COURTESY_CALL_ID) TOTAL_COURTESY_CALL_CAR,
                            count(T_CUSTOMER_VISIT_ID) TOTAL_CUSTOMER_VISIT_CAR,
                            count(KONFIG_FILE_ID) TOTAL_FILE_KONFIGURASI,
                            count(DATEK_FILE_ID) TOTAL_FILE_DATEK,
                            count(EVALUASI_FILE_ID) TOTAL_FILE_EVALUASI,
                            count(CUSTOMER_VISIT) TOTAL_CUSTOMER_VISIT_SALES,
                            count(PROPOSAL_PRESENTASI) TOTAL_PROPOSAL_PRESENTASI
                        from
                            (
                                select
                                    t1.T_COURTESY_CALL_ID,
                                    null T_CUSTOMER_VISIT_ID,
                                    null KONFIG_FILE_ID,
                                    null DATEK_FILE_ID,
                                    null EVALUASI_FILE_ID,
                                    null CUSTOMER_VISIT,
                                    null PROPOSAL_PRESENTASI,
                                    t1.CONTACT_PERSON_ID,
                                    t1.NEW_CONTACT_PERSON_ID,
                                    cast(to_char(t1.REALIZATION_DATE, 'MM') as number) MONTH_NUM,
                                    cast(to_char(t1.REALIZATION_DATE, 'YYYY') as number) YEAR_NUM,
                                    v1.CUSTOMER_ID
                                from
                                    t_courtesy_call t1
                                join
                                (
                                    select 
                                        pic.SITE_PIC_ID CONTACT_PERSON_ID,
                                        --pic.PIC_NAME NAME, 
                                        'N' IS_NEW_CP,
                                        comp.COMPANY_ID CUSTOMER_ID,
                                        comp.COMPANY_NAME
                                    from 
                                        SNI.SITE_PIC pic
                                    LEFT JOIN
                                       sni.site
                                    ON 
                                        pic.site_id = site.site_id
                                    left join
                                        sni.company comp
                                    on
                                        site.company_id = comp.company_id
                                    where
                                        NVL (pic.VALID_TO, SYSDATE + 1) > SYSDATE
                                    UNION ALL
                                    SELECT  
                                        ncp.NEW_CONTACT_PERSON_ID,
                                        --ncp.NAME,
                                        'Y' IS_NEW_CP,
                                        ncp.CUSTOMER_ID,
                                        comp.COMPANY_NAME
                                    FROM      
                                        NEW_CONTACT_PERSON ncp
                                    LEFT JOIN
                                       sni.company comp
                                    ON 
                                       ncp.CUSTOMER_ID = comp.company_id 
								    UNION ALL
                                    SELECT (cpc.COMPANY_PIC_ID + 1000000000) CONTACT_PERSON_ID,
                                        'N' IS_NEW_CP,
                                         comp.COMPANY_ID CUSTOMER_ID,
                                         comp.COMPANY_NAME
                                    FROM sni.COMPANY_PIC cpc
                                        JOIN sni.company comp
                                        ON CPC.COMPANY_ID = comp.COMPANY_ID
                                    WHERE NVL(cpc.VALID_TO,SYSDATE) >= SYSDATE
                                )v1
                                    on (t1.CONTACT_PERSON_ID = v1.CONTACT_PERSON_ID and v1.IS_NEW_CP = 'N')
                                    or (t1.NEW_CONTACT_PERSON_ID = v1.CONTACT_PERSON_ID and v1.IS_NEW_CP = 'Y')
                                where
                                    t1.call_status = 'REALIZED'
                                    and 
                                    (
                                        to_date('1\' || :prmStartMonth || '\' || :prmStartYear, 'dd\mm\yyyy') <= t1.REALIZATION_DATE
                                        and
                                        ADD_MONTHS(to_date('1\' || :prmEndMonth || '\' || :prmEndYear, 'dd\mm\yyyy'), 1) > t1.REALIZATION_DATE
                                    )
                                union all
                                select
                                    null T_COURTESY_CALL_ID,
                                    t1.T_CUSTOMER_VISIT_ID,
                                    null KONFIG_FILE_ID,
                                    null DATEK_FILE_ID,
                                    null EVALUASI_FILE_ID,
                                    null CUSTOMER_VISIT,
                                    null PROPOSAL_PRESENTASI,
                                    t1.CONTACT_PERSON_ID,
                                    t1.NEW_CONTACT_PERSON_ID,
                                    cast(to_char(t1.VISIT_DATETIME, 'MM') as number) MONTH_NUM,
                                    cast(to_char(t1.VISIT_DATETIME, 'YYYY') as number) YEAR_NUM,
                                    v1.CUSTOMER_ID
                                from
                                    t_customer_visit t1
                                join
                                (
                                    select 
                                        pic.SITE_PIC_ID CONTACT_PERSON_ID,
                                        --pic.PIC_NAME NAME, 
                                        'N' IS_NEW_CP,
                                        comp.COMPANY_ID CUSTOMER_ID,
                                        comp.COMPANY_NAME
                                    from 
                                        SNI.SITE_PIC pic
                                    LEFT JOIN
                                       sni.site
                                    ON 
                                        pic.site_id = site.site_id
                                    left join
                                        sni.company comp
                                    on
                                        site.company_id = comp.company_id
                                    where
                                        NVL (pic.VALID_TO, SYSDATE + 1) > SYSDATE
                                    UNION ALL
                                    SELECT  
                                        ncp.NEW_CONTACT_PERSON_ID,
                                        --ncp.NAME,
                                        'Y' IS_NEW_CP,
                                        ncp.CUSTOMER_ID,
                                        comp.COMPANY_NAME
                                    FROM      
                                        NEW_CONTACT_PERSON ncp
                                    LEFT JOIN
                                       sni.company comp
                                    ON 
                                       ncp.CUSTOMER_ID = comp.company_id 
								    UNION ALL
                                    SELECT (cpc.COMPANY_PIC_ID + 1000000000) CONTACT_PERSON_ID,
                                        'N' IS_NEW_CP,
                                         comp.COMPANY_ID CUSTOMER_ID,
                                         comp.COMPANY_NAME
                                    FROM sni.COMPANY_PIC cpc
                                        JOIN sni.company comp
                                        ON CPC.COMPANY_ID = comp.COMPANY_ID
                                    WHERE NVL(cpc.VALID_TO,SYSDATE) >= SYSDATE
                                )v1
                                    on (t1.CONTACT_PERSON_ID = v1.CONTACT_PERSON_ID and v1.IS_NEW_CP = 'N')
                                    or (t1.NEW_CONTACT_PERSON_ID = v1.CONTACT_PERSON_ID and v1.IS_NEW_CP = 'Y')
                                where
                                    t1.visit_status = 'REALIZED'
                                    and 
                                    (
                                        to_date('1\' || :prmStartMonth || '\' || :prmStartYear, 'dd\mm\yyyy') <= t1.VISIT_DATETIME
                                        and
                                        ADD_MONTHS(to_date('1\' || :prmEndMonth || '\' || :prmEndYear, 'dd\mm\yyyy'), 1) > t1.VISIT_DATETIME
                                    )
                                union all
                                select
                                    null T_COURTESY_CALL_ID,
                                    null T_CUSTOMER_VISIT_ID,
                                    KONFIG_FILE_ID,
                                    DATEK_FILE_ID,
                                    EVALUASI_FILE_ID,
                                    null CUSTOMER_VISIT,
                                    null PROPOSAL_PRESENTASI,
                                    null CONTACT_PERSON_ID,
                                    null NEW_CONTACT_PERSON_ID,
                                    cast(to_char(CREATE_DATE, 'MM') as number) MONTH_NUM,
                                    cast(to_char(CREATE_DATE, 'YYYY') as number) YEAR_NUM,
                                    CUSTOMER_ID
                                from
                                    T_KONFIGURASI_DATEK
                                where
                                    to_date('1\' || :prmStartMonth || '\' || :prmStartYear, 'dd\mm\yyyy') <= CREATE_DATE
                                    and
                                    ADD_MONTHS(to_date('1\' || :prmEndMonth || '\' || :prmEndYear, 'dd\mm\yyyy'), 1) > CREATE_DATE
                                union all
                                select
                                    null T_COURTESY_CALL_ID,
                                    null T_CUSTOMER_VISIT_ID,
                                    null KONFIG_FILE_ID,
                                    null DATEK_FILE_ID,
                                    null EVALUASI_FILE_ID,
                                    slx.t_slx_activity_id CUSTOMER_VISIT,
                                    null PROPOSAL_PRESENTASI,
                                    null CONTACT_PERSON_ID,
                                    null NEW_CONTACT_PERSON_ID,
                                    cast(to_char(slx.UPDATE_DATE, 'MM') as number) MONTH_NUM,
                                    cast(to_char(slx.UPDATE_DATE, 'YYYY') as number) YEAR_NUM,
                                    comp.COMPANY_ID CUSTOMER_ID
                                from
                                    slogix.t_slx_activity slx
                                join
                                    sni.COMPANY comp
                                    on
                                    slx.ACCOUNT_NAME = COMP.COMPANY_NAME
                                where
                                    p_activity_type_id = 11
                                    and
                                    TO_DATE ('1\' || :prmStartMonth || '\' || :prmStartYear, 'dd\mm\yyyy') <= slx.UPDATE_DATE
                                    AND ADD_MONTHS (TO_DATE ('1\' || :prmEndMonth || '\' || :prmEndYear,'dd\mm\yyyy'), 1) > slx.UPDATE_DATE
                                union all
                                select
                                    null T_COURTESY_CALL_ID,
                                    null T_CUSTOMER_VISIT_ID,
                                    null KONFIG_FILE_ID,
                                    null DATEK_FILE_ID,
                                    null EVALUASI_FILE_ID,
                                    null CUSTOMER_VISIT,
                                    slx.t_slx_activity_id PROPOSAL_PRESENTASI,
                                    null CONTACT_PERSON_ID,
                                    null NEW_CONTACT_PERSON_ID,
                                    cast(to_char(slx.UPDATE_DATE, 'MM') as number) MONTH_NUM,
                                    cast(to_char(slx.UPDATE_DATE, 'YYYY') as number) YEAR_NUM,
                                    comp.COMPANY_ID CUSTOMER_ID
                                from
                                    slogix.t_slx_activity slx
                                join
                                    sni.COMPANY comp
                                    on
                                    slx.ACCOUNT_NAME = COMP.COMPANY_NAME
                                where
                                    p_activity_type_id = 10
                                    and
                                    TO_DATE ('1\' || :prmStartMonth || '\' || :prmStartYear, 'dd\mm\yyyy') <= slx.UPDATE_DATE
                                    AND ADD_MONTHS (TO_DATE ('1\' || :prmEndMonth || '\' || :prmEndYear,'dd\mm\yyyy'), 1) > slx.UPDATE_DATE    
                            )   
                        where CUSTOMER_ID = :prmID
                        group by
                            MONTH_NUM,
                            YEAR_NUM,
                            CUSTOMER_ID
                        order by
                            YEAR_NUM desc, MONTH_NUM desc
                    ";

                DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());

                AddToCache(sxCacheKey, dt, CacheType.Daily);
                
            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }

//        public DataTable GetPerangkatSeries(int iCompanyId, DateTime dtmStart, DateTime dtmEnd)
//        {
//            return GetPerangkatSeries(iCompanyId, dtmStart, dtmEnd, false);
//        }
//        public DataTable GetPerangkatSeries(int iCompanyId, DateTime dtmStart, DateTime dtmEnd, bool bForceRefresh)
//        {
//            String sxCacheKey = String.Format("{0}{1}_{2}_{3}",
//                KCache.V_REPORT_PERANGKAT_SERIES,
//                iCompanyId.ToString(),
//                dtmStart.ToShortDateString(),
//                dtmEnd.ToShortDateString());

//            if (bForceRefresh)
//            {
//                HttpRuntime.Cache.Remove(sxCacheKey);
//            }

//            if (HttpRuntime.Cache[sxCacheKey] == null)
//            {
//                DBLib dbl = new DBLib(Connection.WEB_USER);

//                List<OracleParameter> lsParams = new List<OracleParameter>();

//                lsParams.Add(new OracleParameter());
//                lsParams[lsParams.Count - 1].ParameterName = "prmID";
//                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
//                lsParams[lsParams.Count - 1].Value = iCompanyId;

//                //lsParams.Add(new OracleParameter());
//                //lsParams[lsParams.Count - 1].ParameterName = "prmStart";
//                //lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
//                //lsParams[lsParams.Count - 1].Value = dtmStart;

//                //lsParams.Add(new OracleParameter());
//                //lsParams[lsParams.Count - 1].ParameterName = "prmEnd";
//                //lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
//                //lsParams[lsParams.Count - 1].Value = dtmEnd;

//                lsParams.Add(new OracleParameter());
//                lsParams[lsParams.Count - 1].ParameterName = "prmStartMonth";
//                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
//                lsParams[lsParams.Count - 1].Value = dtmStart.Month;

//                lsParams.Add(new OracleParameter());
//                lsParams[lsParams.Count - 1].ParameterName = "prmStartYear";
//                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
//                lsParams[lsParams.Count - 1].Value = dtmStart.Year;

//                lsParams.Add(new OracleParameter());
//                lsParams[lsParams.Count - 1].ParameterName = "prmEndMonth";
//                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
//                lsParams[lsParams.Count - 1].Value = dtmEnd.Month;

//                lsParams.Add(new OracleParameter());
//                lsParams[lsParams.Count - 1].ParameterName = "prmEndYear";
//                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
//                lsParams[lsParams.Count - 1].Value = dtmEnd.Year;

//                String sxQuery =
//                    @"
//                      
//                        SELECT   DISTINCT
//                                 v1.PERANGKAT --|| '-' || v1.BAGIAN_PERANGKAT || '-' || v1.PENYEBAB
//                                    AS sebab
//                          FROM                  sni.T_PRE_TROUBLE_REC t1
//                                             LEFT JOIN
//                                                sni.NETWORK net
//                                             ON t1.NETWORK_ID = net.NETWORK_ID
//                                          LEFT JOIN
//                                             sni.NETWORK_SITE m1
//                                          ON t1.NETWORK_ID = m1.NETWORK_ID
//                                       LEFT JOIN
//                                          sni.SITE m2
//                                       ON m1.SITE_ID = m2.SITE_ID
//                                    JOIN
//                                       sni.v_crm_ver_sebab_solusi v1
//                                    ON t1.T_PRE_TROUBLE_REC_ID = v1.T_PRE_TROUBLE_REC_ID
//                                 LEFT JOIN
//                                    sni.COMPANY comp
//                                 ON m2.COMPANY_ID = comp.COMPANY_ID
//                         WHERE       m1.p_network_site_status_id = 4
//                                 AND NVL (m1.valid_to, SYSDATE + 1) > SYSDATE
//                                 AND TO_DATE ('1\' || :prmStartMonth || '\' || :prmStartYear,
//                                              'dd\mm\yyyy') <= RECORD_DATE
//                                 AND ADD_MONTHS (
//                                       TO_DATE ('1\' || :prmEndMonth || '\' || :prmEndYear,
//                                                'dd\mm\yyyy'),
//                                       1
//                                    ) > RECORD_DATE
//                                 AND COMPANY_ID = :prmID  
//                    ";

//                DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());

//                AddToCache(sxCacheKey, dt, CacheType.Daily);

//            }
//            return (DataTable)HttpRuntime.Cache[sxCacheKey];
//        }        

        public override String GetCacheFilename()
        {
            return KCache.Filename.COMPANY_REPORT_VIEWS;
        }
    }
}